/**
 *
 */
package com.angel.dao.generic.jdbc.pooling;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDriver;
import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.impl.GenericObjectPool;
import org.apache.log4j.Logger;

import com.angel.dao.generic.exceptions.GenericDAOException;
import com.angel.dao.generic.jdbc.engine.EngineSintaxQuery;
import com.angel.dao.generic.jdbc.engine.impl.MySQLSintaxQuery;
import com.angel.dao.generic.jdbc.info.JDBCConnectionInfo;

/**
 * @author William
 *
 */
public class JDBCPoolingDriver {

	private static final String APACHE_DBCP_POOLING_DRIVER = "org.apache.commons.dbcp.PoolingDriver";
	private static final Logger LOGGER = Logger.getLogger(JDBCPoolingDriver.class);
	private static final String URI_DBCP_DATA_SOURCE = "jdbc:apache:commons:dbcp:";

	private JDBCConnectionInfo jdbcConnectionInfo;

	private EngineSintaxQuery engineSintaxQuery;

	private PoolingDriver driver;

	private Connection conn;


	public JDBCPoolingDriver(JDBCConnectionInfo jdbcConnectionInfo){
		super();
		this.jdbcConnectionInfo = jdbcConnectionInfo;
		this.engineSintaxQuery = new MySQLSintaxQuery();
	}

	public JDBCPoolingDriver(JDBCConnectionInfo jdbcConnectionInfo, EngineSintaxQuery engineSintaxQuery){
		super();
		this.jdbcConnectionInfo = jdbcConnectionInfo;
		this.engineSintaxQuery = engineSintaxQuery;
	}

	public void initializeConnection() {
        /** First we load the underlying JDBC driver.
         * You need this if you don't use the jdbc drivers system property.
         */
		LOGGER.debug("Loading underlying JDBC driver.");
	    try {
	        Class.forName(this.getJdbcConnectionInfo().getJdbcDriver());
	    } catch (ClassNotFoundException e) {
	        this.throwDataGenerationException(e, "JDBC Driver cannot found [" + this.getJdbcConnectionInfo().getJdbcDriver() + "].");
	    }
	    LOGGER.debug("Loading underlying JDBC driver done.");

	    /**
		    Then we set up and register the PoolingDriver.
		    Normally this would be handled auto-magically by an external configuration,
		    but in this example we'll do it manually.
	    */
	    LOGGER.debug("Setting up driver.");
	    try {
	        this.setupDriver(this.getJdbcConnectionInfo().getConnectionURI());

	        this.createConenction();
	    } catch (Exception e) {
	        this.throwDataGenerationException(e, "An unexpected error occurred setting up jdbc driver with uri connection ["+ this.getJdbcConnectionInfo().getConnectionURI() +"].");
	    }
	    LOGGER.debug("Setting up driver done.");
    }

	private void createConenction() {
        LOGGER.info("Creating connection.");
        try {
			conn = DriverManager.getConnection(URI_DBCP_DATA_SOURCE + this.getJdbcConnectionInfo().getDataSourceName());
		} catch (SQLException e) {
			this.throwDataGenerationException(e, "An unexpected error occurred getting connection.");
		}
	}

	public void execute(String query){
		this.checkValidConnection();
		/**
			Now, we can use JDBC as we normally would.
			Using the connect string jdbc:apache:commons:dbcp:example
        	The general form being:
        		jdbc:apache:commons:dbcp:<name-of-pool>
        */
        Statement stmt = null;
        try {
            LOGGER.debug("Creating statement.");
            stmt = conn.createStatement();
            LOGGER.debug("Executing statement ["+ query +"].");
            stmt.execute(query);
        } catch(SQLException e) {
	        this.throwDataGenerationException(e, "An unexpected error occurred executing query ["+ query +"].");
        } finally {
            try {
				stmt.close();
			} catch (Exception e) {
		        this.throwDataGenerationException(e, "An unexpected error occurred closing statement.");
			}
        }
	}

	private void checkValidConnection() {
		if(this.getDriver() == null){
			this.throwDataGenerationException("Invalid driver connection. First must initialize connection with jdbc driver [Actual JDBC Driver:"+ this.getJdbcConnectionInfo().getJdbcDriver()+"].");
		}

		boolean closedConnection = false;
		try {
			closedConnection = this.getConn().isClosed();
			if(this.getConn() == null && closedConnection){
				this.throwDataGenerationException("Invalid connection [Opened Connection: " + !closedConnection +"]. First must initialize connection with jdbc driver [Actual JDBC Driver:"+ this.getJdbcConnectionInfo().getJdbcDriver()+"].");
			}
		} catch (SQLException e) {
			this.throwDataGenerationException("Invalid connection [Opened Connection: " + !closedConnection +"]. First must initialize connection with jdbc driver [Actual JDBC Driver:"+ this.getJdbcConnectionInfo().getJdbcDriver()+"].");
		}
	}

	public int executeQuery(String query){
		this.checkValidConnection();

		Statement stmt = null;
		ResultSet rset = null;
		int columnsCount = 0;
		try {
			LOGGER.debug("Creating statement.");
            stmt = conn.createStatement();
            LOGGER.debug("Executing statement [" + query + "].");
            rset = stmt.executeQuery(query);
            columnsCount = rset.getMetaData().getColumnCount();
        } catch(SQLException e) {
        	this.throwDataGenerationException(e, "An unexpected error occurred executing query ["+ query +"].");
        } finally {
            try {
            	if(rset != null){
            		rset.close();
            	}
				stmt.close();
			} catch (Exception e) {
				this.throwDataGenerationException(e, "An unexpected error occurred closing resultset and statement.");
			}
        }
        return columnsCount;
	}

	private void throwDataGenerationException(String errorMessage){
    	LOGGER.error(errorMessage);
        throw new GenericDAOException(errorMessage);
	}

	private void throwDataGenerationException(Throwable exception, String errorMessage){
    	LOGGER.error(errorMessage);
    	throw new GenericDAOException(errorMessage, exception);
	}

    public void finalizeConnection() {
        /** Display some pool statistics. */
        try {
            logsDriverStats();
        } catch (Exception e) {
        	this.throwDataGenerationException(e, "An unexpected error occurred display pool statistics.");
        }

        /** Closes the pool. */
        try {
            shutdownDriver();
        } catch (Exception e) {
        	this.throwDataGenerationException(e, "An unexpected error occurred closing pool for driver [" + this.getJdbcConnectionInfo().getJdbcDriver() + "].");
        }

    }

		public void setupDriver(String connectURI) throws Exception {
	        /**
	        	First, we'll need a ObjectPool that serves as the actual pool of connections.
	        	We'll use a GenericObjectPool instance, although any ObjectPool implementation will suffice.
	        */
	        ObjectPool connectionPool = new GenericObjectPool(null);

	        /**
	        	Next, we'll create a ConnectionFactory that the pool will use to create Connections.
	        	We'll use the DriverManagerConnectionFactory, using the connect string passed in the command line arguments.
	        */
			ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(connectURI,this.getJdbcConnectionInfo().getUserName(),this.getJdbcConnectionInfo().getPassword());

	        /** Now we'll create the PoolableConnectionFactory, which wraps the "real" connections created by
	        	the ConnectionFactory with the classes that implement the pooling functionality. */
	        @SuppressWarnings("unused")
			PoolableConnectionFactory poolableConnectionFactory
				= new PoolableConnectionFactory(connectionFactory,connectionPool,null,null,this.getJdbcConnectionInfo().isReadOnly(),this.getJdbcConnectionInfo().isAutoCommit());

	        /** Finally, we create the PoolingDriver itself. */
	        Class.forName(APACHE_DBCP_POOLING_DRIVER);
	        this.driver = (PoolingDriver) DriverManager.getDriver(URI_DBCP_DATA_SOURCE);

	        /** Register our pool with it. */
	        this.getDriver().registerPool(this.getJdbcConnectionInfo().getDataSourceName(),connectionPool);
	    }

		public boolean existTable(String tableName){
			this.checkValidConnection();
			String query = "select * from " + tableName;
			Statement stmt = null;
			ResultSet rset = null;
			boolean existTable = false;
			try {
				LOGGER.debug("Creating statement.");
	            stmt = conn.createStatement();
	            LOGGER.debug("Executing statement [" + query + "].");
	            rset = stmt.executeQuery(query);
	            existTable = true;;
	        } catch(SQLException e) {

	        } finally {
	            try {
	            	if(rset != null){
	            		rset.close();
	            	}
					stmt.close();
				} catch (Exception e) {
					this.throwDataGenerationException(e, "An unexpected error occurred closing resultset and statement.");
				}
	        }
	        return existTable;
		}

	    public void logsDriverStats() throws Exception {
	        ObjectPool connectionPool = this.getDriver().getConnectionPool(this.getJdbcConnectionInfo().getDataSourceName());
	        LOGGER.debug("NumActive: " + connectionPool.getNumActive());
	        LOGGER.debug("NumIdle: " + connectionPool.getNumIdle());
	    }

	    protected void shutdownDriver() throws Exception {
	    	if(this.getDriver() != null){
	    		this.getDriver().closePool(this.getJdbcConnectionInfo().getDataSourceName());
	    	}
	    	if(this.getConn() != null){
	    		this.getConn().close();
	    	}
	        this.setDriver(null);
	        this.setConn(null);
	    }

		/**
		 * @return the driver
		 */
		protected PoolingDriver getDriver() {
			return driver;
		}

		/**
		 * @param driver the driver to set
		 */
		protected void setDriver(PoolingDriver driver) {
			this.driver = driver;
		}

		/**
		 * @return the conn
		 */
		protected Connection getConn() {
			return conn;
		}

		/**
		 * @param conn the conn to set
		 */
		protected void setConn(Connection conn) {
			this.conn = conn;
		}

		/**
		 * @return the jdbcConnectionInfo
		 */
		public JDBCConnectionInfo getJdbcConnectionInfo() {
			return jdbcConnectionInfo;
		}

		/**
		 * @param jdbcConnectionInfo the jdbcConnectionInfo to set
		 */
		public void setJdbcConnectionInfo(JDBCConnectionInfo jdbcConnectionInfo) {
			this.jdbcConnectionInfo = jdbcConnectionInfo;
		}

		/**
		 * @return the engineSintaxQuery
		 */
		public EngineSintaxQuery getEngineSintaxQuery() {
			return engineSintaxQuery;
		}

		/**
		 * @param engineSintaxQuery the engineSintaxQuery to set
		 */
		public void setEngineSintaxQuery(EngineSintaxQuery engineSintaxQuery) {
			this.engineSintaxQuery = engineSintaxQuery;
		}

		public void executeEnableTableContraints(String tableName){
			for(String query: this.getEngineSintaxQuery().buildEnableTableContraintsQuery(tableName) ){
				this.execute(query);
			}
		}

		public void executeDisableTableContraints(String tableName){
			for(String query: this.getEngineSintaxQuery().buildDisableTableContraintsQuery(tableName) ){
				this.execute(query);
			}
		}

		public void executeDropDatabaseTable(String databaseName){
			this.execute(this.getEngineSintaxQuery().buildDropDatabaseQuery(databaseName));
		}

		public void executeDeleteTable(String tableName){
			this.execute(this.getEngineSintaxQuery().buildDeleteTableQuery(tableName));
		}

		public void executeDropTable(String tableName){
			this.execute(this.getEngineSintaxQuery().buildDropTableQuery(tableName));
		}
}
